﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web.UI.WebControls;
namespace DbCommon
{
    public class DbHelper
    {
        string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        /// <summary>
        /// 执行非查询语句
        /// </summary>
        /// <param name="strsql">sql语句</param>
        /// <param name="pr">sql语句参数，可以为空</param>
        /// <returns>返回受影响的行数</returns>
        public int ExcuteNoQuery(string strsql, params SqlParameter[] pr)
        {
            int i = 0;
            using (SqlConnection con = new SqlConnection(constr))
            {
                using (SqlCommand cmd = new SqlCommand(strsql, con))
                {
                    if (pr != null && pr.Length > 0)
                    {
                        cmd.Parameters.AddRange(pr);
                    }
                    con.Open();
                    i = cmd.ExecuteNonQuery();
                    con.Close();
                }
            }
            return i;
        }


        /// <summary>
        /// 执行查询语句
        /// </summary>
        /// <param name="strsql">sql语句</param>
        /// <param name="pr">sql语句单参数</param>
        /// <returns>返回查询的表格</returns>
        public DataTable ExcuteQuery(string strsql, SqlParameter pr)
        {
            DataTable dt = new DataTable();
            using (SqlConnection con = new SqlConnection(constr))
            {
                SqlCommand cmd = new SqlCommand(strsql, con);
                if (pr != null)
                {
                    cmd.Parameters.Add(pr);
                }
                //string ss = cmd.CommandText;
                SqlDataAdapter sda = new SqlDataAdapter(cmd);
                sda.Fill(dt);
            }
            return dt;
        }
        /// <summary>
        /// 执行查询语句
        /// </summary>
        /// <param name="strsql">sql语句</param>
        /// <param name="pr">sql语句参数</param>
        /// <returns>返回查询的表格</returns>
        public DataTable ExcuteQuery(string strsql, params SqlParameter[] pr)
        {
            DataTable dt = new DataTable();
            using (SqlConnection con = new SqlConnection(constr))
            {
                SqlCommand cmd = new SqlCommand(strsql, con);
                if (pr != null && pr.Length > 0)
                {
                    cmd.Parameters.AddRange(pr);
                }
                //string ss = cmd.CommandText;
                SqlDataAdapter sda = new SqlDataAdapter(cmd);

                sda.Fill(dt);
            }
            return dt;
        }
        /// <summary>
        /// 得到表中某个单元的值
        /// </summary>
        /// <param name="sqlColumn">列名</param>
        /// <param name="strCon">查询条件</param>
        /// <returns>返回表中单个单元的值</returns>
        public object GetSingleItem(string tableName, string sqlColumn, string sqlCon)
        {
            //SqlParameter[] pr = new SqlParameter[]{
            //     new SqlParameter("@sqlColumn",sqlColumn),
            //     new SqlParameter("@tableName",tableName),
            //     new SqlParameter("@sqlCon",sqlCon)
            // };
            string strsql = "select " + sqlColumn + " from " + tableName + " where 1=1 and " + sqlCon;
            DataTable dt = ExcuteQuery(strsql);
            //string strsql = "select @sqlColumn from @tableName where 1=1 and @sqlCon";
            //DataTable dt = ExcuteQuery(strsql,pr);
            if (dt.Rows.Count > 0)
            {
                return dt.Rows[0][0];
            }
            else
            {
                return null;
            }
        }
        /// <summary>
        /// 查询列表总的行数
        /// </summary>
        /// <param name="sqlTable">列表名</param>
        /// <returns></returns>
        public int GetCount(string sqlTable)
        {
            return GetCount(sqlTable, "");
        }
        /// <summary>
        /// 查询列表总的行数
        /// </summary>
        /// <param name="sqlTable">表名</param>
        /// <param name="con">条件</param>
        /// <returns>返回总的行数</returns>
        public int GetCount(string sqlTable, string con)
        {
            int i;
            string sqlstr;
            if (!string.IsNullOrEmpty(con))
            {
                sqlstr = "select count(*) from " + sqlTable + " where 1=1 and " + con;
            }
            else
            {
                sqlstr = "select count(*) from " + sqlTable;
            }
            DataTable dt = ExcuteQuery(sqlstr);
            if (int.TryParse(dt.Rows[0][0].ToString(), out i))
            {
                return i;
            }
            else
                return 0;
        }

        /// <summary>
        /// 根据相应的条件来获得分页表格
        /// </summary>
        /// <param name="sqlTable">表格名称</param>
        /// <param name="startIndex">开始项</param>
        /// <param name="endIndex">结束项</param>
        /// <param name="orderCon">排序条件</param>
        /// <param name="sqlCon">查询条件</param>
        /// <returns>返回分页表格</returns>
        public DataTable GetPageTable(string sqlTable, int startIndex, int endIndex, string orderCon, string sqlCon)
        {
            StringBuilder stb = new StringBuilder();
            stb.Append("select * from ( ");
            stb.Append("select *, Row_Number() over( order by ");
            if (!string.IsNullOrEmpty(orderCon))
            {
                stb.Append(orderCon);
            }
            stb.Append(") rownum from ");
            stb.Append(sqlTable);
            stb.Append(" where 1=1 ");
            if (!string.IsNullOrEmpty(sqlCon))
            {
                stb.Append("and " + sqlCon);
            }
            stb.Append(" ) t");
            stb.Append(" where t.rownum>= " + startIndex);
            stb.Append(" and t.rownum<= " + endIndex);
            DataTable dt = ExcuteQuery(stb.ToString());
            return dt;
        }

        public DataTable ExcuteQuery(StringBuilder sql, SqlParameter[] parameter)
        {
            throw new NotImplementedException();
        }

        /// <summary>
        /// 执行存存储过程
        /// </summary>
        /// <param name="StoredProcedureName"></param>
        /// <param name="Params"></param>
        /// <returns></returns>
        public  DataSet ExecuteStoredProcedureNonQuery(string StoredProcedureName, params SqlParameter[] Params)
        {
            DataSet ds = new DataSet();
            using (SqlConnection con = new SqlConnection(constr))
            {
                SqlCommand cmd = new SqlCommand(StoredProcedureName, con);
                if (Params != null)
                {
                    cmd.Parameters.Add(Params);
                }
                cmd.CommandType = CommandType.StoredProcedure;
                string ss = cmd.CommandText;
                SqlDataAdapter sda = new SqlDataAdapter(cmd);
                sda.Fill(ds);
            }
            return ds;
        }

        /// <summary>
        /// 执行SP_Page分页存储过程
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="fieldOrder"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <param name="records"></param>
        /// <returns></returns>
        public DataTable ExecuteSp_Page(string strSql, string fieldOrder, int pageSize, int pageIndex, out int records)
        {
            DataTable dt = null;         //返回的数据集   
            records = 0;    //事先赋值

            using (SqlConnection sqlConn = new SqlConnection(constr))
            {
                //打开连接
                sqlConn.Open();

                //初始化参数


                SqlCommand sqlCmd = new SqlCommand();
                sqlCmd.Connection = sqlConn;
                sqlCmd.CommandText = "SP_Page";
                sqlCmd.CommandType = CommandType.StoredProcedure;

                #region  ___存储过程参数___
                SqlParameter recordsParam = new SqlParameter("@TotalCount", SqlDbType.Int, 32);
                recordsParam.Direction = ParameterDirection.Output;
                //创建
                sqlCmd.Parameters.Add(new SqlParameter("@Sql", SqlDbType.NVarChar, 1024));
                sqlCmd.Parameters.Add(new SqlParameter("@Sort", SqlDbType.NVarChar, 100));
                sqlCmd.Parameters.Add(new SqlParameter("@PageSize", SqlDbType.Int, 32));
                sqlCmd.Parameters.Add(new SqlParameter("@PageIndex", SqlDbType.Int, 32));
                sqlCmd.Parameters.Add(recordsParam);
                //赋值
                sqlCmd.Parameters[0].Value = strSql;
                sqlCmd.Parameters[1].Value = fieldOrder;
                sqlCmd.Parameters[2].Value = pageSize;
                sqlCmd.Parameters[3].Value = pageIndex;
                sqlCmd.Parameters[4].Direction = ParameterDirection.Output;
                #endregion ___存储过程参数___

                //取数据
                DataSet ds = new DataSet();
                SqlDataAdapter sda = new SqlDataAdapter(sqlCmd);
                sda.Fill(ds);
                if (ds != null && ds.Tables.Count > 0)
                {
                    dt = ds.Tables[0];
                }

                records = (int)recordsParam.Value;  //返回记录数

                //释放资源
                if (sqlConn != null)
                {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }

            }


            return dt;
        }


    }
}
